package io.sdph.order.dao;

import io.sdph.core.database.Transaction;
import io.sdph.core.database.exceptions.DatabaseException;
import io.sdph.documents.model.Invoice;
import io.sdph.documents.model.InvoiceType;
import io.sdph.documents.model.PaymentMethod;
import io.sdph.order.model.Order;
import io.sdph.products.model.Product;
import io.sdph.userprofile.model.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

@Repository
public class OrderManagerDB implements OrderManagerDAO {

	private static final Logger log = Logger.getLogger(OrderManagerDB.class);

	public Order order(final Map<Product, Long> products,
			final Map<String, String> properties, final Invoice invoice) {
		Order order = null;

		try {
			Transaction tr = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {
					Order order = null;

					int orderID = insert(
							"INSERT INTO Orderr (number) VALUES ('?');",
							invoice.getNumber());

					if (properties != null) {
						for (Entry<String, String> entry : properties
								.entrySet()) {
							String key = entry.getKey();
							String value = entry.getValue();

							insert("INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES ('?','?','?');",
									orderID, key, value);

						}
					}

					order = new Order(Integer.toString(orderID));
					order.setProducts(products);
					order.setProperties(properties);
					order.setInvoice(new Invoice(invoice.getNumber()));
					return order;
				}
			};
			order = (Order) tr.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return order;
	}

	public boolean cancelOrder(final Order order) {
		int czyUsunieto = 0;
		try {
			Transaction tr = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {

					int czyUsunieto = delete(
							"DELETE FROM Orderr where numberOrderr=?",
							order.getNumber());

					delete("DELETE FROM Properties WHERE numberOrderr=?",
							order.getNumber());

					return czyUsunieto;
				}
			};
			czyUsunieto = (Integer) tr.getResult();

		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (czyUsunieto > 0) {
			return true;
		} else {

			return false;
		}
	}

	public boolean cancelOrder(final String orderId) {
		int czyUsunieto = 0;
		try {
			Transaction tr = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {

					int czyUsunieto = delete(
							"DELETE FROM Orderr where numberOrderr=?", orderId);

					delete("DELETE FROM Properties WHERE numberOrderr=?",
							orderId);

					return czyUsunieto;
				}
			};
			czyUsunieto = (Integer) tr.getResult();

		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (czyUsunieto > 0) {
			return true;
		} else {

			return false;
		}

	}

	public List<Order> getUserOrders(final User user) {
    	
    	List<Order> orders = null;
    	
    	
    	
    	try {
			Transaction tr = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					List<Order> orders =new ArrayList<Order>();
					Order order = null;
					ResultSet rs = select("select * from Orderr where Orderr.number=ANY(select Invoice.number from Invoice "+
					"where  idUserr=(select idUserr from Userr where idUserr=?))", user.getId());
					
					while(rs.next()){
						
						order = new Order(rs.getString("numberOrderr"));
						//invoice
						order.setInvoice(new Invoice(rs.getString("number")));
						
						//products
						Map<Product, Long> products = null;
						Product product = null;
						ResultSet rs2 = select("Select Products.idProduct , amount from Products, Product where Products.idProduct=Product.idProduct"+
						" and Products.number=(select number from Orderr where numberOrderr=?)", rs.getString("numberOrderr"));

						while (rs2.next()) {

							product = new Product(rs2.getString("idProduct"));

							products.put(product, rs2.getLong("amount"));
						}

						order.setProducts(products);
						//properities
						
						ResultSet rs3 = select(
								"select * from Properties where numberOrderr=?;",
								order.getNumber());

						Map<String, String> properities = new HashMap<String, String>();
						while (rs3.next()) {
							properities.put(rs3.getString("keyy"),
									rs3.getString("value"));
						}
						order.setProperties(properities);
						
						orders.add(order);
					}
					
					return orders;
				}
			};
			orders=(List<Order>)tr.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	
    	
    	return orders;}

	public List<Order> getUserOrderHistory(final User user, final Date dateFrom, final Date dateTo) {


    	List<Order> orders = null;
    	
    	
    	
    	try {
			Transaction tr = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					List<Order> orders =new ArrayList<Order>();
					Order order = null;
					//TODO
					ResultSet rs = select("select * from Orderr where Orderr.number=ANY(select Invoice.number from Invoice "+
					"where Invoice.creationDate > '2013-12-26' and Invoice.creationDate <'2013-12-30' and idUserr=(select idUserr from Userr where idUserr=3))", user.getId(),dateFrom,dateTo);
					
					while(rs.next()){
						
						order = new Order(rs.getString("numberOrderr"));
						//invoice
						order.setInvoice(new Invoice(rs.getString("number")));
						
						//products
						Map<Product, Long> products = null;
						Product product = null;
						ResultSet rs2 = select("Select Products.idProduct , amount from Products, Product where Products.idProduct=Product.idProduct"+
						" and Products.number=(select number from Orderr where numberOrderr=?)", rs.getString("numberOrderr"));

						while (rs2.next()) {

							product = new Product(rs2.getString("idProduct"));

							products.put(product, rs2.getLong("amount"));
						}

						order.setProducts(products);
						//properities
						
						ResultSet rs3 = select(
								"select * from Properties where numberOrderr=?;",
								order.getNumber());

						Map<String, String> properities = new HashMap<String, String>();
						while (rs3.next()) {
							properities.put(rs3.getString("keyy"),
									rs3.getString("value"));
						}
						order.setProperties(properities);
						
						orders.add(order);
					}
					
					return orders;
				}
			};
			orders=(List<Order>)tr.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	
    	
    	return orders;
	}

	@Override
	public void fill(final Order order) {

		try {
			Transaction tr = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {

					ResultSet rs = select(
							"select * from Orderr where numberOrderr=?;",
							order.getNumber());

					if (rs.next()) {

						order.setInvoice(new Invoice(rs.getString("number")));
					}

					// /////////////
					Map<Product, Long> products = null;
					Product product = null;
					ResultSet rs2 = select("Select Products.idProduct , amount from Products, Product where Products.idProduct=Product.idProduct"+
					" and Products.number=(select number from Orderr where numberOrderr=?)", order.getNumber());

					while (rs2.next()) {

						product = new Product(rs2.getString("idProduct"));

						products.put(product, rs2.getLong("amount"));
					}

					order.setProducts(products);

					ResultSet rs3 = select(
							"select * from Properties where numberOrderr=?;",
							order.getNumber());

					Map<String, String> properities = new HashMap<String, String>();
					while (rs3.next()) {
						properities.put(rs3.getString("keyy"),
								rs3.getString("value"));
					}
					order.setProperties(properities);

					return null;
				}
			};
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public Order getOrder(String orderId) {
		// TODO Auto-generated method stub
		return null;
	}

}
